package com.blog.spark.dao

import java.sql.{Connection, PreparedStatement}

import com.blog.spark.entity.{BrowserOsTimes, DayAreaTimes, DayIpAreaTimes, DayOsTimes}
import com.blog.spark.utils.MysqlUtils

import scala.collection.mutable.ListBuffer

/**
  * @description 各个维度统计DAO
  * @author yuyon26@126.com
  * @date 2018/10/6 15:24
  */
object StatDao {


  /**
    * 批量插入（ip地区统计）
    *
    * @param list
    */
  def insertIPAreaBatch(list: ListBuffer[DayIpAreaTimes]) = {

    var connection: Connection = null
    var pstmt: PreparedStatement = null
    try {
      connection = MysqlUtils.getConnetction()

      val sql = "insert into day_ip_area_times_stat(day,ip,area,times) values (?,?,?,?) "
      pstmt = connection.prepareStatement(sql)

      //关闭自动提交
      connection.setAutoCommit(false)
      for (data <- list) {
        pstmt.setString(1, data.day)
        pstmt.setString(2, data.ip)
        pstmt.setString(3, data.area)
        pstmt.setLong(4, data.times)
        pstmt.addBatch()
      }
      //执行批处理
      pstmt.executeBatch()
      connection.commit()
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      MysqlUtils.release(connection, pstmt)
    }
  }
  /**
    * 批量插入（地区统计）
    *
    * @param list
    */
  def insertAreaBatch(list: ListBuffer[DayAreaTimes]) = {

    var connection: Connection = null
    var pstmt: PreparedStatement = null
    try {
      connection = MysqlUtils.getConnetction()

      val sql = "insert into day_area_times_stat(day,area,times) values (?,?,?) "
      pstmt = connection.prepareStatement(sql)

      //关闭自动提交
      connection.setAutoCommit(false)
      for (data <- list) {
        pstmt.setString(1, data.day)
        pstmt.setString(2, data.area)
        pstmt.setLong(3, data.times)
        pstmt.addBatch()
      }
      //执行批处理
      pstmt.executeBatch()
      connection.commit()
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      MysqlUtils.release(connection, pstmt)
    }
  }

  /**
    * 批量插入（os平台统计）
    *
    * @param list
    */
  def insertOsBatch(list: ListBuffer[DayOsTimes]) = {

    var connection: Connection = null
    var pstmt: PreparedStatement = null
    try {
      connection = MysqlUtils.getConnetction()

      val sql = "insert into day_os_times_stat(day,os,times) values (?,?,?) "
      pstmt = connection.prepareStatement(sql)

      //关闭自动提交
      connection.setAutoCommit(false)
      for (data <- list) {
        pstmt.setString(1, data.day)
        pstmt.setString(2, data.os)
        pstmt.setLong(3, data.times)
        pstmt.addBatch()
      }
      //执行批处理
      pstmt.executeBatch()
      connection.commit()
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      MysqlUtils.release(connection, pstmt)
    }
  }

  /**
    * 批量插入（浏览器统计）
    *
    * @param list
    */
  def insertBrowserBatch(list: ListBuffer[BrowserOsTimes]) = {

    var connection: Connection = null
    var pstmt: PreparedStatement = null
    try {
      connection = MysqlUtils.getConnetction()

      val sql = "insert into day_browser_times_stat(day,browser,times) values (?,?,?) "
      pstmt = connection.prepareStatement(sql)

      //关闭自动提交
      connection.setAutoCommit(false)
      for (data <- list) {
        pstmt.setString(1, data.day)
        pstmt.setString(2, data.browser)
        pstmt.setLong(3, data.times)
        pstmt.addBatch()
      }
      //执行批处理
      pstmt.executeBatch()
      connection.commit()
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      MysqlUtils.release(connection, pstmt)
    }
  }

  /**
    * 删除指定日期的数据
    *
    * @param day
    */
  def deleteDate(day: String) = {

    var connection: Connection = null
    var pstmt: PreparedStatement = null
    val tables = Array("day_ip_area_times_stat", "day_os_times_stat", "day_browser_times_stat","day_area_times_stat")
    try {
      connection = MysqlUtils.getConnetction()
      tables.foreach(table => {
        val deleteSql = s"delete from ${table} where day = ?"
        pstmt = connection.prepareStatement(deleteSql)
        pstmt.setString(1, day)
        pstmt.executeUpdate()
      })
    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      MysqlUtils.release(connection, pstmt)
    }

  }

}
